
## sqlite demo

import sqlite3
from typing import List


def find_students_by_score_range(db_path: str, min_score: float, max_score: float) -> List[str]:
    """
    根据分数段查找学生名字

    :param db_path: 数据库文件路径
    :param min_score: 最低分数(包含)
    :param max_score: 最高分数(包含)
    :return: 符合条件的学生名字列表
    """
    try:
        # 连接数据库
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()

        # 执行查询
        cursor.execute("""
            SELECT name FROM students 
            WHERE score BETWEEN ? AND ?
            ORDER BY score DESC
        """, (min_score, max_score))

        # 获取结果
        results = [row[0] for row in cursor.fetchall()]

        return results

    except sqlite3.Error as e:
        print(f"数据库错误: {e}")
        return []
    finally:
        # 确保连接关闭
        if conn:
            conn.close()


# 使用示例
if __name__ == "__main__":
    # 先创建测试数据库
    def create_test_db():
        conn = sqlite3.connect(":memory:")  # 内存数据库，仅用于测试
        cursor = conn.cursor()
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS students (
                id INTEGER PRIMARY KEY,
                name TEXT NOT NULL,
                score REAL NOT NULL
            )
        """)

        # 插入测试数据
        test_data = [
            ("张三", 85.5),
            ("李四", 92.0),
            ("王五", 78.0),
            ("赵六", 88.5),
            ("钱七", 95.0)
        ]
        cursor.executemany("INSERT INTO students (name, score) VALUES (?, ?)", test_data)
        conn.commit()
        return conn


    # 测试
    test_conn = create_test_db()
    test_db_path = ":memory:"  # 内存数据库

    print("80-90分的学生:")
    print(find_students_by_score_range(test_db_path, 80, 90))

    print("\n90分以上的学生:")
    print(find_students_by_score_range(test_db_path, 90, 100))

    test_conn.close()

#  demo
class User(object):
    def __init__(self,id,name):
        self.id = id
        self.name = name
[
    User('1', 'Michael'),
    User('2', 'Bob'),
    User('3', 'Adam')
]
